package cn.com.lms.business.learning.service;

import cn.com.lms.business.core.constant.BNZConstants;
import cn.com.lms.business.core.constant.LearningStateEnum;
import cn.com.lms.business.core.constant.SortEnum;
import cn.com.lms.business.core.util.DateUtils;
import cn.com.lms.business.core.util.FBUtils;
import cn.com.lms.business.core.util.SystemUserUtil;
import cn.com.lms.business.course.service.FbCourseInfoService;
import cn.com.lms.business.learning.persistence.entity.FbLearningPlan;
import cn.com.lms.business.learning.persistence.entity.FbLearningStage;
import cn.com.lms.business.learning.persistence.entity.FbLearningStageDetail;
import cn.com.lms.business.learning.persistence.entity.FbLearningStudent;
import cn.com.lms.business.learning.persistence.entity.vo.*;
import cn.com.lms.business.learning.persistence.repository.LearningPlanRepository;
import cn.com.lms.business.learning.persistence.repository.LearningStageDetailRepository;
import cn.com.lms.business.learning.persistence.repository.LearningStageRepository;
import cn.com.lms.business.learning.persistence.repository.LearningStudentRepository;
import cn.com.lms.business.organization.persistence.repository.FbSysDepartmentRepository;
import cn.com.lms.business.questionnaire.persistence.repository.UserQuestionnaireRepository;
import cn.com.lms.business.user.persistence.entity.SysUserVo;
import com.bnzj.core.exception.BusinessException;
import com.bnzj.core.export.Column;
import com.bnzj.core.persistence.BaseService;
import com.bnzj.core.persistence.query.CompareExpression;
import com.bnzj.core.persistence.query.CompareType;
import com.bnzj.core.persistence.query.IExpression;
import com.bnzj.core.persistence.query.QueryHelpBean;
import com.bnzj.core.rest.ResponseResult;
import com.bnzj.core.rest.ResultCode;
import com.bnzj.core.webmvc.util.WebMvcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.persistence.NoResultException;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;


@Service
@Transactional
public class LearningPlanService extends BaseService<LearningPlanRepository, FbLearningPlan, Long> {

    private static final Logger logger = LoggerFactory.getLogger(LearningPlanService.class);

    private static final List<Column> columns = new ArrayList<Column>();

    static {
        columns.add(new Column("account", "工号", 0));
        columns.add(new Column("userName", "姓名", 1));
        columns.add(new Column("deptName", "所在部门", 2));
        columns.add(new Column("identity", "身份证", 3));
        columns.add(new Column("phone", "手机号", 4));
        columns.add(new Column("jobType", "岗位分类", 5));
        columns.add(new Column("completionStatus", "完成度(%)", 6));
        columns.add(new Column("learningHours", "实际学时(min)", 7));
        columns.add(new Column("lastStudyTime", "最近学习时间", 8));
        columns.add(new Column("lastStudyTime", "实际完成时间", 9));
        columns.add(new Column("finishTime", "要求完成时间", 10));
        columns.add(new Column("personnelText", "在职状态", 11));
        columns.sort(null);

    }

    private static final List<Column> appointColumns = new ArrayList<Column>();

    static {
        appointColumns.add(new Column("account", "工号", 0));
        appointColumns.add(new Column("userName", "姓名", 1));
        appointColumns.add(new Column("phone", "手机号", 1));
        appointColumns.add(new Column("identity", "身份证号", 1));
        appointColumns.add(new Column("deptName", "所在部门", 2));
        appointColumns.add(new Column("jobType", "岗位分类", 5));
        appointColumns.add(new Column("completionStatus", "完成度(%)", 6));
        appointColumns.add(new Column("learningHours", "实际学时(min)", 7));
        appointColumns.add(new Column("lastStudyTime", "最近学习时间", 8));
        appointColumns.add(new Column("lastFinishTime", "实际完成时间", 9));
        appointColumns.add(new Column("finishTime", "要求完成时间", 10));
        appointColumns.add(new Column("personnelText", "在职状态", 11));
        appointColumns.sort(null);
    }

    @Autowired
    private LearningStageRepository stageRepository;

    @Autowired
    private LearningStudentRepository studentRepository;

    @Autowired
    private LearningStageDetailRepository stageDetailRepository;

    @Autowired
    private FbCourseInfoService courseInfoService;

    @Autowired
    private FbSysDepartmentRepository sysDepartmentRepository;

    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    /*
     * @author liuwei
     * @date 2020/3/18 17:17
     * @desc 创建学习计划
     */
    @Autowired
    private TransactionTemplate transactionTemplate;

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public ResponseResult saveLearning(LearningSaveVo learningSaveVo) throws Exception {
        SysUserVo sysUser = SystemUserUtil.getCurrentUser();
        //实体类赋属性值
        FbLearningPlan fbLearningPlan = new FbLearningPlan();
        //如果是修改需要判断状态是待发布才允许修改

        if (null != learningSaveVo.getId() && 0 != learningSaveVo.getId()) {
            FbLearningPlan plan = this.repository.getOne(learningSaveVo.getId());
            //判断学习计划状态，非待发布不允许操作
            if (!plan.getStateId().equals(getStateId(LearningStateEnum.NO_RELEASE.getCode()))) {
                return ResponseResult.failure(0, "学习计划已发布，不允许修改!");
            }
            //每次修改都需要清空计划下的数据重新添加
            studentRepository.delByPlanId(learningSaveVo.getId());
            this.stageRepository.delByPlanId(learningSaveVo.getId());
            this.stageDetailRepository.deleteByPlanId(learningSaveVo.getId());
            fbLearningPlan.setModifyTime(new Date());
            fbLearningPlan.setModifyBy(String.valueOf(sysUser.getId()));
            fbLearningPlan.setModifyName(sysUser.getCname());
        } else {
            fbLearningPlan.setId(null);
            fbLearningPlan.setCreateTime(new Date());
            fbLearningPlan.setCreator(String.valueOf(sysUser.getId()));
            fbLearningPlan.setCreatorName(sysUser.getCname());
        }
        if (!CollectionUtils.isEmpty(learningSaveVo.getLearningPlanDeptInfo())) {
            List<Long> deptId = learningSaveVo.getLearningPlanDeptInfo().stream().filter(s -> s.getDeptId() != 0).map(LearningPlanDeptInfo::getDeptId).collect(Collectors.toList());
            List<String> jobType = learningSaveVo.getLearningPlanDeptInfo().stream().filter(s -> s.getDeptId() == 0).map(LearningPlanDeptInfo::getDeptName).collect(Collectors.toList());
            String pId = "";
            for (Long str : deptId) {
                pId += str + ",";
            }
            //部门和岗位分开查询学员，因后面查看详情的时候需要按两者名称分组显示
            List<LearningPlanDeptInfo> list = this.exportAll(queryByDeptOrJobType(pId.substring(0, pId.length() - 1), null));
            for (String str : jobType) {
                list.addAll(this.exportAll(queryByDeptOrJobType(null, str)));
            }
            learningSaveVo.getLearningStudentList().addAll(list);
        }
        //阶段详情集合
        BeanUtils.copyProperties(learningSaveVo, fbLearningPlan);
        //设置状态值
        fbLearningPlan.setStateId(getStateId(learningSaveVo.getOperationCode()));
        fbLearningPlan.setIsDelete(BNZConstants.DB_NO_DELETE);
        fbLearningPlan.setIsOver(BNZConstants.DB_NO_OVER);
        List<FbLearningStudent> learningStudentList = new ArrayList<>();

        if (LearningStateEnum.IS_RELEASE.getCode().equals(learningSaveVo.getOperationCode())) {
            fbLearningPlan.setReleaseTime(new Date());
        }

        FbLearningPlan plan = repository.save(fbLearningPlan);
        learningSaveVo.getLearningStageList().stream().forEach(item -> {
            item.setPlanId(plan.getId());
            item.setIsDelete("0");
            //保存阶段信息记录
            FbLearningStage stage = stageRepository.save(item);
            item.getStageDetails().forEach(detail -> {
                detail.setStageId(stage.getId());
                detail.setPlanId(plan.getId());
                detail.setObjName(detail.getObjTitle());
                FbLearningStageDetail stageDetail = stageDetailRepository.save(detail);
                //新增阶段学员viewLearning
                learningSaveVo.getLearningStudentList().stream().forEach(students -> {
                    FbLearningStudent student = new FbLearningStudent();
                    students.setStageDetailId(stageDetail.getId());
                    students.setLearningHours(0);
                    students.setPlanId(plan.getId());
                    students.setFinishState("0");
                    BeanUtils.copyProperties(students, student);
                    learningStudentList.add(student);
                });
            });
        });
        new Thread(new PlanSaveService(learningStudentList)).start();
        return ResponseResult.success();
    }

    class PlanSaveService implements Runnable {

        private List<FbLearningStudent> learningStudentList = new ArrayList<>();

        public PlanSaveService(List<FbLearningStudent> studentsList) {
            this.learningStudentList = studentsList;
        }


        @Override
        public void run() {
            List<FbLearningStudent> studentList = new ArrayList<>();
            int size = learningStudentList.size();
            int num = (size) % 500 == 0 ? (size / 500) : (size / 500 + 1);
            int end = 0;
            int start = 0;
            for (int i = 1; i <= num; i++) {
                end = (i * 500) > size ? size : (i * 500);
                start = (i - 1) * 500;
                studentList = learningStudentList.subList(start, end);
                studentRepository.saveAll(studentList);
            }
        }


    }

    /*
     * @author liuwei
     * @date 2020/3/23 9:25
     * @desc 查看计划详情
     */
    public ResponseResult getLearningView(Long learningId) {

        FbLearningPlan learningPlan = this.repository.getOne(learningId);
        if (null == learningPlan) {
            return ResponseResult.bizException(new BusinessException("数据不存咋", ResultCode.FAILURE));
        }
        LearningViewVo viewVo = new LearningViewVo();
        BeanUtils.copyProperties(learningPlan, viewVo);
        List<FbLearningStage> stageList = stageRepository.queryStageByPlanId(learningId);
        stageList.forEach(stage -> {
            try {
                List<Map<String, Object>> mapList = stageDetailRepository.queryListByStageId(stage.getId());
                List<FbLearningStageDetail> stageDetails = FBUtils.convertListMapToListBean(mapList, FbLearningStageDetail.class);
                stage.setStageDetails(stageDetails);
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        });
        List<Map<String, Object>> deptList = studentRepository.queryDeptInfoByLearningId(learningId);
        List<Map<String, Object>> studentList = null;
//        if (CollectionUtils.isEmpty(deptList)) {
        studentList = studentRepository.queryByLearningId(learningId);
//        }

        viewVo.setStageList(stageList);
        viewVo.setStudentList(studentList);
        viewVo.setDeptList(deptList);
        return ResponseResult.success(viewVo);
    }

    /*
     * @author liuwei
     * @date 2020/3/19 15:27
     * @desc 根据操作code获取发布状态id
     */
    public Long getStateId(Integer operationCode) {
        List<Map<String, Object>> maps = courseInfoService.getCourseTypeDictionary(BNZConstants.LEARNING_STATE_CODE);
        String value = LearningStateEnum.getValueByCode(operationCode);
        for (Map<String, Object> map : maps) {
            if (map.get("name").toString().equals(value)) {
                return Long.valueOf(String.valueOf(map.get("value")));
            }
        }
        return null;
    }

    @Transactional(rollbackFor = Exception.class)
    public ResponseResult overlearning(Long id) {
        this.repository.updateOverById(id);
        return ResponseResult.success();
    }


    /*
     * @author liuwei
     * @date 2020/3/19 11:03
     * @desc 学习计划列表
     */
    public Page<FbLearningPlan> queryLearningList(HttpServletRequest request) {
        //学习计划名称
        String planName = request.getParameter("planName");
        //状态
        String stateId = request.getParameter("stateCode");
        Pageable pageable = WebMvcUtils.initPage(request, Sort.by(Sort.Direction.DESC, "create_time"));
        QueryHelpBean queryHelpBean = getQueryHelpBean();
        queryHelpBean.setPageable(pageable);

        if (!StringUtils.isEmpty(planName)) {
            IExpression notIdsCondition = CompareExpression.build("lp.plan_name", CompareType.like, planName);
            queryHelpBean.addWhere(notIdsCondition);
        }
        if (!StringUtils.isEmpty(stateId)) {
            IExpression notIdsCondition = CompareExpression.build("lp.state_id", CompareType.equal, stateId);
            queryHelpBean.addWhere(notIdsCondition);
        }
        Page<FbLearningPlan> all1 = this.findAll(queryHelpBean);
        return all1;
    }

    /**
     * 删除学习计划
     *
     * @param id
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResponseResult delLearning(Long id, Integer type) {
        if (BNZConstants.TYPE_ONE.equals(type)) {
            this.repository.updateDelById(id);
        }
        return ResponseResult.success();
    }

    /*
     * @author liuwei
     * @date 2020/3/19 16:23
     * @desc 修改学习计划状态
     */
    public ResponseResult updateLearningState(Long learningId, Integer code) {
        SysUserVo sysUser = SystemUserUtil.getCurrentUser();

        FbLearningPlan learningPlan = this.repository.getOne(learningId);
        learningPlan.setModifyTime(new Date());
        //状态为发布修改发布时间
        if (LearningStateEnum.IS_RELEASE.getCode().equals(code)) {
            learningPlan.setReleaseTime(new Date());
        }
        learningPlan.setModifyName(sysUser.getCname());
        learningPlan.setModifyBy(String.valueOf(sysUser.getId()));
        learningPlan.setStateId(getStateId(code));
        this.repository.save(learningPlan);
        return ResponseResult.success();
    }

    /*
     * @author liuwei
     * @date 2020/3/19 16:58
     * @desc 追踪学习计划信息
     */
    public ResponseResult<LearningViewVo> getTrackLearningView(Long learningId, HttpServletRequest request) {
        LearningViewVo learningSaveVo = new LearningViewVo();
        Map param = this.repository.getPlanDetailById(learningId);
        List<FbLearningStage> stageList = stageRepository.queryStageByPlanId(learningId);
        stageList.forEach(stage -> {
            try {
                List<Map<String, Object>> mapList = stageDetailRepository.queryListByStageId(stage.getId());
                List<FbLearningStageDetail> stageDetails = FBUtils.convertListMapToListBean(mapList, FbLearningStageDetail.class);
                stage.setStageDetails(stageDetails);
            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        });
        try {
            Object obj = LearningViewVo.class.newInstance();
            org.apache.commons.beanutils.BeanUtils.populate(obj, param);
            learningSaveVo = (LearningViewVo) obj;
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        learningSaveVo.setStageList(stageList);
        return ResponseResult.success(learningSaveVo);

    }

    /*
     * @author liuwei
     * @date 2020/3/19 16:58
     * @desc 按学员查看阶段详情
     */
    public Page<LearningByStudentVo> getLearningByStudent(Long learningId, HttpServletRequest request) {
        String finishState = request.getParameter("finishState");
        String templateName = request.getParameter("templateName");
        String isRecord = request.getParameter("isRecord");
        Pageable pageable = WebMvcUtils.initPage(request, Sort.by(Sort.Direction.DESC, "lastStudyTime"));
        QueryHelpBean queryHelpBean = getQueryStudentHelpBean(learningId, finishState, templateName, isRecord);
        queryHelpBean.setPageable(pageable);
        Page<LearningByStudentVo> all1 = this.findAll(queryHelpBean);
        return all1;

    }

    /**
     * 查询课程学习计划明细列表
     *
     * @param id
     * @return
     */
    public ResponseResult<LearningStageViewStuVo> getCourseLearningView(Long id, HttpServletRequest request) {
        String finishState = request.getParameter("finishState");
        Pageable pageable = WebMvcUtils.initPage(request);
        QueryHelpBean queryHelpBean = getQueryLearningCourseHelpBean(id);
        IExpression notIdsCondition = CompareExpression.build("ls.finish_state", CompareType.equal, finishState);
        queryHelpBean.addWhere(notIdsCondition);
        queryHelpBean.setPageable(pageable);
        Page<Map<String, Object>> all1 = this.findAll(queryHelpBean);
        LearningStageViewStuVo stageViewStuVo = new LearningStageViewStuVo();
        stageViewStuVo.setFinishNum(studentRepository.getCountByStageDetailId(id, 1));
        stageViewStuVo.setUnFinishNum(studentRepository.getCountByStageDetailId(id, 0));
        stageViewStuVo.setPage(all1);
        return ResponseResult.success(stageViewStuVo);
    }

    /*
     * @author liuwei
     * @date 2020/3/20 16:25
     * @desc 按员工查看列表详情
     */
    public ResponseResult<List<FbLearningStage>> getCourseLearningByStuView(HttpServletRequest request) {
        Long planId = Long.valueOf(request.getParameter("planId"));
        Long userId = Long.valueOf(request.getParameter("userId"));
        List<FbLearningStage> stageList = stageRepository.queryStageByPlanId(planId);
        stageList.forEach(item -> {
            List<Map<String, Object>> mapList = stageDetailRepository.queryListByStudentId(userId, item.getId());
            try {
                List<FbLearningStageDetail> stageDetails = FBUtils.convertListMapToListBean(mapList, FbLearningStageDetail.class);
                item.setStageDetails(stageDetails);
                item.getStageDetails().forEach(detail -> {
                    if (BNZConstants.TYPE_ONE.equals(Integer.valueOf(detail.getStudyType()))) {
                        BigDecimal starNum = new BigDecimal(detail.getStudyTime());
                        BigDecimal endNum = new BigDecimal(detail.getFinishState());
                        Integer num = (starNum.divide(endNum, 2, BigDecimal.ROUND_HALF_UP)).multiply(new BigDecimal(100)).intValue();
                        if (num > 100) {
                            num = 100;
                        }
                        detail.setFinishState(String.valueOf(num));
                    } else if ("1".equals(detail.getFinishState())) {
                        detail.setFinishState(String.valueOf(100));
                    }
                });
            } catch (Exception e) {
                e.printStackTrace();
                logger.error(e.getMessage());
            }
        });
        return ResponseResult.success(stageList);
    }


    /*
     * @author liuwei
     * @date 2020/3/23 18:05
     * @desc APP查看用户的学习计划
     */
    public ResponseResult<Page<UserLearningListVo>> queryUserLearningPlanList(HttpServletRequest request) {
        Pageable pageable = WebMvcUtils.initPage(request, Sort.by(Sort.Direction.DESC, "lp.create_time"));
        String finisState = request.getParameter("finishState");
        QueryHelpBean queryHelpBean = queryUserLearningPlan(SystemUserUtil.getCurrentUser().getId(), finisState);
        queryHelpBean.setPageable(pageable);
        Page<UserLearningListVo> all1 = null;
        try {
            all1 = this.findAll(queryHelpBean);
        } catch (NoResultException e) {
            all1 = new PageImpl(new ArrayList<UserLearningListVo>(), queryHelpBean.getPageable(), 0);
        }
        return ResponseResult.success(all1);
    }


    /*
     * @author liuwei
     * @date 2020/3/23 18:05
     * @desc APP查看用户的学习计划阶段详情
     */
    public ResponseResult<UserLearningPlanVo> getUserLearningPlan(Long learningId) {
        Long userId = SystemUserUtil.getCurrentUser().getId();
        Map<String, Object> param = studentRepository.getUserLearningPlan(userId, learningId);
        UserLearningPlanVo planVo = new UserLearningPlanVo();
        Object obj = null;
        try {
            obj = UserLearningPlanVo.class.newInstance();
            org.apache.commons.beanutils.BeanUtils.populate(obj, param);
            planVo = (UserLearningPlanVo) obj;
            //查询阶段信息集合
            List<FbLearningStage> stages = stageRepository.queryStageByPlanId(learningId);
            try {
                String date = com.bnzj.core.util.DateUtils.formatDate(new Date(), "yyyy-MM-dd");
                Date date1 = com.bnzj.core.util.DateUtils.parseDate(date);
                int days = DateUtils.daysBetween(date1, planVo.getFinishTime());
                planVo.setDiffDays(days);
                if (planVo.getFinishTime().getTime() < date1.getTime()) {
                    planVo.setDiffDays(0);
                }

            } catch (ParseException e) {
                e.printStackTrace();
                return null;
            }
            stages.forEach(stage -> {
                try {
                    //查询阶段下的详情
                    List<Map<String, Object>> mapList = stageDetailRepository.queryStageDetail(stage.getId(), userId);
                    List<FbLearningStageDetail> stageDetails = FBUtils.convertListMapToListBean(mapList, FbLearningStageDetail.class);
                    stage.setStageDetails(stageDetails);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
            planVo.setStageList(stages);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return ResponseResult.success(planVo);
    }

    /*
     * @author liuwei
     * @date 2020/3/25 17:01
     * @desc 统计学习计划数据列表
     */
    public Page getPlanStatisticsList(HttpServletRequest request) {
        String planName = request.getParameter("planName");
        String startDate = request.getParameter("startDate");
        String endDate = request.getParameter("endDate");
        Pageable pageable = WebMvcUtils.initPage(request, Sort.by(Sort.Direction.DESC, "a.release_time"));
        QueryHelpBean queryHelpBean = learningPlanStatisticsList();
        IExpression nameCondition = CompareExpression.build("a.plan_name", CompareType.like, planName);

        if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
            startDate += " 00:00:00";
            endDate += " 23:59:59";
        }
        IExpression startTimeCondition = CompareExpression.build("a.release_time", CompareType.between, "releaseTime", startDate, endDate);
        queryHelpBean.addWhere(nameCondition, startTimeCondition);
        queryHelpBean.setPageable(pageable);
        Page<Map<String, Object>> all1 = this.findAll(queryHelpBean);
        all1.forEach(item -> {
            BigDecimal val = new BigDecimal(String.valueOf(item.get("finishNum")));
            BigDecimal va2 = new BigDecimal(String.valueOf(item.get("joinNum")));
            item.put("completionPercentage", 0);
            if (va2.intValue() > 0) {
                item.put("completionPercentage", (val.divide(va2, 2, BigDecimal.ROUND_HALF_UP)).multiply(new BigDecimal(100)).intValue());
            }
        });

        return all1;
    }

    /*
     * @author liuwei
     * @date 2020/4/16 10:11
     * @desc 记录学员参加学习计划记录
     */
    @Transactional
    public ResponseResult<String> savePlanJoinRecord(Long planId) {
        SysUserVo sysUser = SystemUserUtil.getCurrentUser();
        this.repository.savePlanJoinRecord(planId, sysUser.getId(), sysUser.getCname());
        return ResponseResult.success();
    }

    @Transactional
    public void addPlanJoinRecord(Long planId) {
        SysUserVo sysUser = SystemUserUtil.getCurrentUser();
        this.repository.savePlanJoinRecord(planId, sysUser.getId(), sysUser.getCname());
    }

    /*
     * @author liuwei
     * @date 2020/4/24 9:57
     * @desc 跟踪详情页面-按阶段查看-详情-考试
     */
    public Page<StageUserExamListVo> queryStageUserExamList(HttpServletRequest request, Long stageDetailId) {
        String userName = request.getParameter("userName");
        String isRecord = request.getParameter("isRecord");
        String dateSort = request.getParameter("dateSort");
        Pageable pageable = WebMvcUtils.initPage(request, null != dateSort ? Sort.by(dateSort.equals(SortEnum.SORT_ASC.getValue()) ? Sort.Direction.ASC : Sort.Direction.DESC, "ls.last_study_time") : null);
        QueryHelpBean queryHelpBean = queryStageExamDetail(isRecord, userName, stageDetailId);
        queryHelpBean.setPageable(pageable);
        Page<StageUserExamListVo> all1 = this.findAll(queryHelpBean);
        return all1;
    }

    /*
     * @author liuwei
     * @date 2020/4/26 15:39
     * @desc 查询跟踪详情页面-按阶段查看-详情-问卷列表
     */
    public Page<StageUserExamListVo> queryStageUserQuestion(HttpServletRequest request, Long stageDetailId) {
        String userName = request.getParameter("userName");
        String dateSort = request.getParameter("dateSort");
        Integer state = request.getParameter("state") == null ? null : Integer.valueOf(request.getParameter("state"));
        Pageable pageable = WebMvcUtils.initPage(request, null != dateSort ? Sort.by(dateSort.equals(SortEnum.SORT_ASC.getValue()) ? Sort.Direction.ASC : Sort.Direction.DESC, "ls.last_study_time") : null);
        QueryHelpBean queryHelpBean = queryStageUserQuestion(state, userName, stageDetailId);
        queryHelpBean.setPageable(pageable);
        Page<StageUserExamListVo> all1 = this.findAll(queryHelpBean);
        return all1;
    }

    @Autowired
    private UserQuestionnaireRepository questionnaireRepository;

    /*
     * @author liuwei
     * @date 2020/4/27 9:28
     * @desc  查询跟踪详情页面-按阶段查看-详情-问卷列表详情
     */
    public Set<StageUserQuestion> getStageUserQuestion(HttpServletRequest request, Long stageDetailId) {
        Long userId = Long.valueOf(request.getParameter("userId"));
        Long questionnaireId = Long.valueOf(request.getParameter("questionnaireId"));
        List<Map<String, Object>> mapList = questionnaireRepository.getStageUserQuestion(stageDetailId, userId, questionnaireId);
        List<StageUserQuestion> userQuestionList = null;
        List<StageUserQuestionSub> subList = null;
        List<StageUserQuestionAnswer> answerList = null;
        Set<StageUserQuestion> userQuestions = new HashSet<>();
        try {
            userQuestionList = FBUtils.convertListMapToListBean(mapList, StageUserQuestion.class);
            userQuestions.addAll(userQuestionList);
            for (StageUserQuestion userQuestion : userQuestions) {
                Set<StageUserQuestionAnswer> answerSet = new HashSet<>();
                List<Map<String, Object>> list = mapList.stream().filter(map -> userQuestion.getQuesId().equals(Long.valueOf(map.get("questionnaireSubId").toString()))).collect(Collectors.toList());
                List<Map<String, Object>> answerMapList = mapList.stream().filter(map -> userQuestion.getQuesId().equals(Long.valueOf(map.get("questionnaireSubId").toString()))).collect(Collectors.toList());
                subList = FBUtils.convertListMapToListBean(list, StageUserQuestionSub.class);
                answerList = FBUtils.convertListMapToListBean(answerMapList, StageUserQuestionAnswer.class);
                answerSet.addAll(answerList);
                //封装用户选项
                userQuestion.setStageUserQuestionAnswer(answerSet);
                //题目选项
                userQuestion.setStageUserQuestionSub(subList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userQuestions;
    }

    /*
     * @author liuwei
     * @date 2020/4/16 16:17
     * @desc 修改学习计划状态为已完成
     */
    public void updatePlanFinishState(Long planId) {
//        查看学习计划状态大于1则为完成
        int num = this.repository.getFinishState(planId);
        if (num > 0) {
            Optional<FbLearningPlan> optional = this.findById(planId);
            if (!optional.isPresent()) {
                return;
            }
            FbLearningPlan learningPlan = optional.get();
            learningPlan.setStateId(getStateId(LearningStateEnum.FINISH.getCode()));
            this.repository.save(learningPlan);
        }
    }

    public FbLearningPlan getFinishStatePlanInfo(Long planId) {
        int num = this.repository.getFinishState(planId);
        if (num > 0) {
            Optional<FbLearningPlan> optional = this.findById(planId);
            return optional.get();
        }
        return null;
    }


    /*
     * @author liuwei
     * @date 2020/3/24 16:58
     * @desc PC学习计划列表
     */
    public QueryHelpBean getQueryHelpBean() {
        String selectSQL = " SELECT lp.id,lp.plan_name,lp.create_time,lp.release_time releaseTime,(select count(DISTINCT lt.user_id) from bnz_learning_student lt where lt.plan_id=lp.id ) AS joinNum, " +
                " IFNULL(flsd.finishNum,0) finishNum,lp.state_id,sd.name stateName,DATEDIFF(finish_time,lp.release_time)+1 as studyDays,lp.creator_name as creator  ";
        String whereSQL = " FROM bnz_learning_plan lp left join bnz_sys_dictionary sd on lp.state_id=sd.id " +
                " LEFT JOIN (SELECT\ta.plan_id,\t\tsum( CASE WHEN a.joinNum = finishNum THEN 1 ELSE 0 END ) finishNum \n" +
                "FROM   (SELECT\tls.plan_id,count( DISTINCT ls.stage_detail_id ) joinNum,\t\tsum( CASE WHEN ls.finish_state = 1 THEN 1 ELSE 0 END ) finishNum " +
                "\tFROM\tbnz_learning_student ls\tGROUP BY\tls.plan_id,ls.user_id ) a GROUP BY\ta.plan_id ) flsd ON flsd.plan_id = lp.id" +
                " where lp.is_delete=0 ";
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(1)" + whereSQL);
        return queryBean;
    }

    /*
     * @author liuwei
     * @date 2020/4/29 18:08
     * @desc //按部门、岗位类型查询学员
     */
    public QueryHelpBean queryByDeptOrJobType(String deptId, String jobType) {
        String selectSQL = "";
        String whereSQL = "";
        if (!StringUtils.isEmpty(deptId)) {
            selectSQL = " select   s.dept_id deptId,s.id userId,s.cname userName,d.full_name deptName";
            whereSQL = "  from bnz_sys_user s left join bnz_sys_department d on s.dept_id=d.id  where dept_id in(" + deptId + ") and personnel=1  ";
        }
        if (!StringUtils.isEmpty(jobType)) {
            selectSQL = "select   s.dept_id deptId,s.id userId,s.cname userName, \"" + jobType + "\" as deptName";
            whereSQL = " from bnz_sys_user s where job_type_name ='" + jobType + "' and personnel=1";
        }
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(1)" + whereSQL, LearningPlanDeptInfo.class);
        return queryBean;
    }

    /*
     * @author liuwei
     * @date 2020/3/20 15:26
     * @desc 按学员查看列表sql
     */
    public QueryHelpBean getQueryStudentHelpBean(Long learningId, String finishState, String templateName, String isRecord) {
        StringBuffer sb = new StringBuffer();
        String sql = "select a.*,(a.finishOff/a.sumNum)*100 as completionStatus from";
        sb.append("( SELECT ls.id,\n" +
                " max(ls.last_study_time) lastStudyTime,su.is_record isRecord," +
                " sum(ls.learning_hours) learning_hours,su.personnel," +
                "ls.user_name,ls.user_id,ls.plan_id,0 as studyTime," +
                "(select lp.finish_time from bnz_learning_plan lp where lp.id=ls.plan_id) finishTime," +
                "su.full_dept_name as deptName,su.account,su.phone,su.identity,su.job_type_name jobType," +
                "(select count(id) from bnz_learning_student lst where lst.user_id=ls.user_id  and lst.plan_id=" + learningId + "  ) as sumNum, " +
                "( select count(id) from bnz_learning_student lst where lst.user_id=ls.user_id and lst.finish_state=0  and lst.plan_id=" + learningId + " ) AS finishOn," +
                "( select count(id) from bnz_learning_student lst where lst.user_id=ls.user_id and lst.finish_state=1 and lst.plan_id=" + learningId + "  ) AS finishOff " +
                " FROM bnz_learning_student ls left join bnz_sys_user su on su.id = ls.user_id   where ls.plan_id=" + learningId + " GROUP BY ls.user_id) a ");
        sb.append(" where 1=1");
        //查询是否备案员工信息
        if (!StringUtils.isEmpty(isRecord)) {
            sb.append(" and a.isRecord ='" + isRecord + "'");
        }
        if (!StringUtils.isEmpty(finishState) && BNZConstants.FINISH_STATE.equals(finishState)) {
            sb.append(" and a.sumNum = a.finishOff");
        }
        if (!StringUtils.isEmpty(finishState) && BNZConstants.UNFINISH_STATE.equals(finishState)) {
            sb.append(" and a.sumNum != a.finishOff");
        }
        if (!StringUtils.isEmpty(finishState) && BNZConstants.DELAY_COMPLETION.equals(finishState)) {
            sb.append(" and a.sumNum = a.finishOff and a.lastStudyTime>(select finish_time from bnz_learning_plan where id=" + learningId + ")");
        }
        if (!StringUtils.isEmpty(templateName)) {
            sb.append(" and (a.deptName like '%" + templateName + "%' or a.user_name like '%" + templateName + "%')");
        }
        QueryHelpBean queryBean = new QueryHelpBean(sql + sb.toString(), "select count(1) from " + sb.toString(), LearningByStudentVo.class);
        return queryBean;
    }

    /**
     * 查看学习阶段课程明细列表sql
     *
     * @return
     */
    public QueryHelpBean getQueryLearningCourseHelpBean(Long id) {
        String selectSQL = " SELECT  id,user_name,( SELECT queryDeptFullName(su.dept_id) FROM bnz_sys_user su WHERE su.id = ls.user_id )  AS deptName   ";
        String whereSQL = " FROM bnz_learning_student ls where ls.stage_detail_id=" + id + "";
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(1)" + whereSQL);
        return queryBean;
    }

    /*
     * @author liuwei
     * @date 2020/3/23 17:38
     * @desc 查看APP用户学习计划列表sql
     */
    public QueryHelpBean queryUserLearningPlan(Long userId, String finishState) {
        String selectSQL = "SELECT lp.id,lp.is_over,lp.plan_name planName,lp.score,lp.create_time createTime," +
                "lp.finish_time finishTime,lp.cover_url coverUrl,sum(ls.learning_hours) hours," +
                " (select sum(course_time) from bnz_course_info where  FIND_IN_SET(id,GROUP_CONCAT( case when study_type = 0 then ld.obj_id else 0 end ))) as sumHours ";
        String whereSQL = " FROM bnz_learning_student ls INNER JOIN bnz_learning_stage_detail ld ON ls.stage_detail_id = ld.id " +
                " INNER JOIN bnz_learning_plan lp ON ls.plan_id = lp.id  " +
                " WHERE lp.is_delete = 0 AND ls.user_id = " + userId + " GROUP BY ls.plan_id ";

        //判断整个学习计划是否完成，判断条件为统计学习计划阶段下的课程学习进度
        if (BNZConstants.UNFINISH_STATE.equals(finishState)) {
            whereSQL += " HAVING (select count(lsd.id) from bnz_learning_student lsd where lsd.user_id = user_id and lsd.plan_id=ls.plan_id and lsd.finish_state=1) !=" +
                    " (select count(lsd.id) from bnz_learning_student lsd where lsd.user_id = user_id and lsd.plan_id=ls.plan_id ) ";
        } else if (BNZConstants.FINISH_STATE.equals(finishState)) {
            whereSQL += "HAVING (select count(lsd.id) from bnz_learning_student lsd where lsd.user_id = user_id and lsd.plan_id=ls.plan_id and lsd.finish_state=1) = " +
                    "(select count(lsd.id) from bnz_learning_student lsd where lsd.user_id = user_id and lsd.plan_id=ls.plan_id )";
        }
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL + " ", "select count(1) from(SELECT DISTINCT ls.plan_id " + whereSQL + ") s", UserLearningListVo.class);
        return queryBean;
    }

    /**
     * 统计学习计划列表SQL
     *
     * @return
     */
    public QueryHelpBean learningPlanStatisticsList() {
        String selectSQL = "select * from ";
        String whereSQL = "( SELECT lp.id,lp.plan_name,lp.release_time,lp.finish_time,\n" +
                "\t\tlp.creator_name,lp.create_time,lp.score,DATEDIFF( finish_time, release_time )+1 diffDays,\n" +
                "count( DISTINCT fls.user_id ) joinNum,count(DISTINCT pj.user_id) inJoinNum," +
                "count(DISTINCT  CASE WHEN   lstd.lastStudyTime <= lp.finish_time THEN lstd.user_id ELSE null END ) finishOnTime," +
                "count(DISTINCT CASE WHEN lstd.lastStudyTime > lp.finish_time THEN lstd.user_id ELSE null END ) delayCompletion," +
                "\t\tIFNULL(flsd.finishNum,0) finishNum\n" +
                "\tFROM bnz_learning_plan lp\n" +
                "\t\tLEFT JOIN (\tSELECT\tls.plan_id,ls.last_study_time,ls.user_id,ls.finish_state,\tls.stage_detail_id,\tls.learning_hours FROM bnz_learning_student ls ) fls ON fls.plan_id = lp.id \n" +
                "\t\tLEFT JOIN (SELECT a.plan_id,sum( CASE WHEN a.joinNum = finishNum THEN 1 ELSE 0 END ) finishNum FROM\t(SELECT ls.plan_id,count( DISTINCT ls.stage_detail_id ) joinNum,\n" +
                "\t\t\tsum( CASE WHEN ls.finish_state = 1 THEN 1 ELSE 0 END ) finishNum FROM bnz_learning_student ls GROUP BY ls.plan_id,ls.user_id ) a GROUP By a.plan_id\n" +
                "\t\t) flsd ON flsd.plan_id = lp.id left join (\tselect plan_id,ls.user_id,max(last_study_time)lastStudyTime,count(ls.stage_detail_id) stageNum," +
                "sum(case when finish_state=1 then 1 else 0 end) finishNum from bnz_learning_student ls     GROUP BY user_id,plan_id HAVING stageNum=finishNum)lstd on lstd.plan_id=lp.id\n" +
                " left join bnz_learning_plan_join pj on pj.plan_id=lp.id WHERE release_time IS NOT NULL  GROUP BY lp.id ) a where 1=1";
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(a.id) from" + whereSQL);
        return queryBean;
    }


    public QueryHelpBean userStatisticsQuery() {
        String selectSQL = " select * from ";
        String whereSQL = "(SELECT su.id,su.account,su.identity,su.phone,su.cname,lp.create_time createTime,sd.name,su.job_type_name jobType,su.is_record isRecord" +
                ",sum(case finish_state when 0 then 0 else learning_hours end)learningHours,(select sum(ci.score) from bnz_course_info" +
                " ci where ci.id in(select obj_id from  bnz_learning_stage_detail lsd where lsd.id in(select stage_detail_id from" +
                " bnz_learning_student ls where ls.user_id=su.id and ls.finish_state=1))) score, (select count(ci.id) from bnz_course_info ci" +
                " where ci.id in(select obj_id from  bnz_learning_stage_detail lsd where lsd.id in(select stage_detail_id from bnz_learning_student ls" +
                " where ls.user_id=su.id and ls.finish_state=1))) courseCount,count(ls.plan_id) planCount,(select count(lsd.id) from bnz_learning_stage_detail lsd" +
                " where lsd.study_type=2 and  FIND_IN_SET(lsd.id, GROUP_CONCAT(ls.stage_detail_id))) examCount" +
                " from bnz_sys_user su inner join bnz_learning_student ls on ls.user_id=su.id left join bnz_learning_plan lp on ls.plan_id=lp.id    left join bnz_sys_department sd on sd.id=su.dept_id where su.personnel=1  GROUP BY su.id ) a where 1=1";
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL, "select count(DISTINCT a.id) from" + whereSQL, UserStatisticsListVo.class);
        return queryBean;
    }

    /*
     * @author liuwei
     * @date 2020/4/24 9:37
     * @desc 跟踪详情页面-按阶段查看-详情-学员考试列表-sql
     */
    public QueryHelpBean queryStageExamDetail(String isRecord, String userName, Long stageDetailId) {
        String selectSQL = "SELECT lsd.id detailId,lsd.obj_id objId,ls.user_id userId,IFNULL(su.is_record,'否') isRecord,ls.user_name userName,queryDeptFullName ( su.dept_id ) deptName," +
                "ls.last_study_time lastStudyTime,ep.grade grade,ep.adopt_score adoptScore,count(ep.id) examNum";
        String whereSQL = " FROM bnz_learning_student ls LEFT JOIN bnz_learning_stage_detail lsd ON ls.stage_detail_id = lsd.id LEFT JOIN bnz_sys_user su ON su.id = ls.user_id" +
                " left join bnz_exam_employee_paper_stage eps on eps.stage_detail_id=ls.stage_detail_id and ls.user_id=eps.employee_id" +
                " left join bnz_exam_employee_paper ep on ep.id=eps.employee_paper_id WHERE lsd.study_type = 2  and ls.stage_detail_id=" + stageDetailId + "";
        if (!StringUtils.isEmpty(userName)) {
            whereSQL += " and ls.user_name like '" + userName + "%'";
        }
        if (!StringUtils.isEmpty(isRecord)) {
            whereSQL += " and su.is_record = '" + isRecord + "'";
        }
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL + " GROUP BY ls.id,lsd.obj_id", "select count(1) " + whereSQL, StageUserExamListVo.class);
        return queryBean;
    }

    /*
     * @author liuwei
     * @date 2020/4/24 9:37
     * @desc 跟踪详情页面-按阶段查看-详情-学员问卷列表-sql
     */
    public QueryHelpBean queryStageUserQuestion(Integer state, String userName, Long stageDetailId) {
        String selectSQL = "SELECT lsd.id detailId,lsd.obj_id objId,ls.user_id userId,ls.user_name userName,queryDeptFullName ( su.dept_id ) deptName,ls.last_study_time lastStudyTime,qi.questionnaire_name questionName,qi.questionnaire_desc questionDesc";
        String whereSQL = " FROM bnz_learning_student ls LEFT JOIN bnz_learning_stage_detail lsd ON ls.stage_detail_id = lsd.id" +
                " LEFT JOIN bnz_sys_user su ON su.id = ls.user_id left join bnz_questionnaire_info qi on qi.id=lsd.obj_id WHERE lsd.study_type = 1 AND ls.stage_detail_id = " + stageDetailId + " ";
        if (!StringUtils.isEmpty(userName)) {
            whereSQL += " and ls.user_name like '" + userName + "%'";
        }
        if (null != state) {
            whereSQL += " and ls.finish_state =" + state;
        }
        QueryHelpBean queryBean = new QueryHelpBean(selectSQL + whereSQL + " GROUP BY ls.id,lsd.obj_id", "select count(1) " + whereSQL, StageUserExamListVo.class);
        return queryBean;
    }


    public static List<Column> getColumnList() {
        return Collections.unmodifiableList(columns);
    }

    public static List<Column> getAppointColumnList() {
        return Collections.unmodifiableList(appointColumns);
    }

    public ResponseResult judgeLearningOver(Long learningId) {
        Long userId = SystemUserUtil.getCurrentUser().getId();
        List<Long> objIdList = repository.getObjIdList(learningId, userId);
        boolean flag = false;
        for (Long courseId : objIdList) {
            String courseOver = repository.getCourseOver(courseId);
            if ("0".equals(courseOver)) {
                return ResponseResult.success("还有课程未完成");
            } else {
                flag = true;
            }
        }
        if (flag) {
            this.repository.updateOverById(learningId);
            return ResponseResult.success("该计划下的所有课程已完成");
        }
        return ResponseResult.success();
    }
}
